package com.easy.modules.sysmgr.service.impl;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.easy.common.dao.DBUtilsHelper;
import com.easy.common.dao.IBaseDao;
import com.easy.common.util.BeanUtils;
import com.easy.common.util.Const;
import com.easy.common.util.file.ConfigUtil;
import com.easy.common.util.file.FileUtils;
import com.easy.common.util.string.HtmlUtil;
import com.easy.common.web.page.DataGrid;
import com.easy.common.web.page.Msg;
import com.easy.common.web.page.Pager;
import com.easy.common.web.springmvc.RealPathResolver;
import com.easy.modules.sysmgr.entity.BackupDBEntity;
import com.easy.modules.sysmgr.service.DataBaseServiceI;
import com.easy.modules.sysmgr.web.form.BackupDBForm;


@Service @Transactional
public class DataBaseServiceImpl implements DataBaseServiceI {
	
	private final Log log = LogFactory.getLog(DataBaseServiceImpl.class); 
	
	@Autowired
	private RealPathResolver realPathResolver ;
	
	@Autowired
	private DBUtilsHelper dbUtil ;
	
	@Autowired
	private IBaseDao<BackupDBEntity> basedaoBackup ;

	@Override
	public String getDefaultCatalog() {
		return this.dbUtil.getDataBase() ;
	}

	@Override
	public List<Map<String, Object>> showDataBases() {
		List<Map<String, Object>> query = new ArrayList<Map<String,Object>>() ;
		try {
			query = this.dbUtil.getRunner().query("show databases", new MapListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}

	@Override
	public List<Map<String, Object>> showTables(BackupDBForm form) {
		List<Map<String, Object>> query = new ArrayList<Map<String,Object>>() ;
		try {
			if(null != form.getDatabaseName() && !"".equals(form.getDatabaseName())) {
				this.dbUtil.getRunner().update("use " + form.getDatabaseName()) ;
			}
			query = this.dbUtil.getRunner().query("show tables", new MapListHandler()) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}

	@Override
	public List<Map<String, Object>> tableAttr(BackupDBForm form) {
		List<Map<String, Object>> query = new ArrayList<Map<String,Object>>() ;
		try {
			if(null == form.getDatabaseName() || "".equals(form.getDatabaseName())) {
				form.setDatabaseName(this.getDefaultCatalog()) ;
			}
			this.dbUtil.getRunner().update("use " + form.getDatabaseName()) ;
			query = this.dbUtil.getRunner().query("desc "+ form.getTable(), new MapListHandler()) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}

	@Override
	public Map<String, Object> tableScript(BackupDBForm form) {
		if(null == form.getDatabaseName() || "".equals(form.getDatabaseName())) {
			form.setDatabaseName(this.getDefaultCatalog()) ;
		}
		Map<String, Object> script = new HashMap<String, Object>() ;
		try {
			this.dbUtil.getRunner().update("use " + form.getDatabaseName()) ;
			Map<String, Object> query = this.dbUtil.getRunner().query("show create table "+ form.getTable(), new MapHandler()) ;
			for(String key : query.keySet()) {
				script.put(HtmlUtil.filterAllWhite(key), query.get(key)) ;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return script;
	}

	@Override
	public List<Map<String, Object>> tablesInformation(BackupDBForm form) {
		List<Map<String, Object>> query = new ArrayList<Map<String,Object>>() ;
		try {
			if(null == form.getDatabaseName() || "".equals(form.getDatabaseName())) {
				form.setDatabaseName(this.getDefaultCatalog()) ;
			}
			query = this.dbUtil.getRunner().query("SELECT TABLE_NAME,ENGINE,TABLE_ROWS,CREATE_TIME,TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA='"+form.getDatabaseName()+"'", new MapListHandler()) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}

	private String getPath() {
		return new File(this.realPathResolver.get(File.separator)).getParent() + File.separator ;
	}
	
	@Override
	public Msg backupDataBase(BackupDBForm form) {
		if(null == form.getDatabaseName() || "".equals(form.getDatabaseName())) {
			form.setDatabaseName(this.getDefaultCatalog()) ;
		}
		
		SimpleDateFormat sd = new SimpleDateFormat("yyyyMMddHHmmss");
		String fineName = form.getDatabaseName()+"-你好" + sd.format(new Date());
		String databaseFullname = fineName + ".sql";
		
		// 数据库帐号
		String user = ConfigUtil.get("username") ; 		
		// 登陆密码
		String password = ConfigUtil.get("password") ;	
		//备份的目录
		String path = getPath() + Const.DB_BACKUP_PATH + form.getDatabaseName() + File.separator ; 
		// 备份的路径地址
		String filepath = path + databaseFullname ; 
		
		File filePathSql = new File(path);
		if (!filePathSql.exists()) {
			filePathSql.mkdirs();
		}
		
		StringBuffer sbs = new StringBuffer();
		sbs.append("mysqldump" + " " + form.getDatabaseName());
		sbs.append(" -h localhost ") ;
		sbs.append(" -u "+user) ;
		sbs.append(" -p" + password) ;
		sbs.append(" --default-character-set=utf8 --result-file=" + filepath) ;
		
		try {
			BackupDBEntity entity = new BackupDBEntity() ;
			entity.setDatabaseName(form.getDatabaseName()) ;
			entity.setDatabaseFullName(databaseFullname) ;
			entity.setFolder(Const.DB_BACKUP_PATH + form.getDatabaseName() + File.separator) ;
			entity.setFullPath(Const.DB_BACKUP_PATH + form.getDatabaseName() + File.separator + databaseFullname) ;
			entity.setCreated(new Date()) ;
			entity.setModifyName(form.getModifyName()) ;
			
			this.basedaoBackup.add(entity) ;
			
			Runtime runtime = Runtime.getRuntime();
			runtime.exec(sbs.toString());
			
			log.info("数据库【"+form.getDatabaseName()+"】备份成功###路径：" + filepath) ;
		} catch (IOException e) {
			log.error("数据库【"+form.getDatabaseName()+"】备份失败") ;
			return new Msg(false, "备份数据库【"+form.getDatabaseName()+"】失败！") ;
		}
		return new Msg(true, "备份数据库【"+form.getDatabaseName()+"】成功！") ;
	}

	@Override
	public Msg backupTables(BackupDBForm form) {
		return null;
	}

	@Override
	public Msg recoverDatabase(BackupDBForm form) {
		
		BackupDBEntity entity = this.basedaoBackup.load(BackupDBEntity.class, form.getId()) ;
		
		if(null != entity) {
			// 数据库帐号
			String user = ConfigUtil.get("username") ; 		
			// 登陆密码
			String password = ConfigUtil.get("password") ;	
			// SQL脚本文件的存放路径
			String filepath = getPath() + entity.getFullPath() ; 
			
			StringBuffer sbs = new StringBuffer();
			sbs.append("mysql");
			sbs.append(" -u" + user) ;
			sbs.append(" -p" + password) ;
			sbs.append(" --default-character-set=utf8") ;
			sbs.append(" " + entity.getDatabaseName() + " < " + filepath) ;
			
			try {
				Runtime runtime = Runtime.getRuntime();
				runtime.exec(sbs.toString());
				
				log.info("数据库【"+entity.getDatabaseName()+"】恢复成功") ;
			} catch (IOException e) {
				log.error("数据库【"+form.getDatabaseName()+"】恢复失败") ;
				return new Msg(false, "恢复数据库【"+entity.getDatabaseName()+"】失败！") ;
			}
			return new Msg(true, "恢复数据库【"+entity.getDatabaseName()+"】成功！") ;
		} else {
			return new Msg(false, "恢复数据库失败，无法找到恢复脚本！") ;
		}
	}

	@Override
	public Msg recoverTables(BackupDBForm form) {
		return null;
	}

	@Override
	public DataGrid datagrid(BackupDBForm form) {
		DataGrid dg = new DataGrid() ;
		
		List<BackupDBForm> forms = new ArrayList<BackupDBForm>() ;
		Pager<BackupDBEntity> pager = this.find(form) ;
		for (BackupDBEntity entity : pager.getDataRows()) {
			BackupDBForm f = new BackupDBForm() ;
			BeanUtils.copyNotNullProperties(entity, f) ;
			forms.add(f) ;
		}
		
		dg.setTotal(pager.getTotal());
		dg.setRows(forms);
		
		return dg;
	}
	
	private Pager<BackupDBEntity> find(BackupDBForm form) {
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = "select t from BackupDBEntity t where 1=1";
		hql = addWhere(hql, form, params);
		return this.basedaoBackup.find(hql, params);
	}
	
	private String addWhere(String hql, BackupDBForm form, Map<String, Object> params) {
		if (null != form) {
			if (form.getId() != null && !"".equals(form.getId())) {
				hql += " and t.id=:id";
				params.put("id", form.getId());
			}
		}
		return hql;
	}

	@Override
	public Msg deleteBackup(BackupDBForm form) {
		BackupDBEntity entity = this.basedaoBackup.load(BackupDBEntity.class, form.getId()) ;
		
		if(FileUtils.delFile(getPath() + entity.getFullPath())) {
			this.basedaoBackup.delete(BackupDBEntity.class, entity.getId()) ;
			return new Msg(true, "备份文件删除成功！") ;
		} else {
			return new Msg(false, "备份文件删除失败！") ;
		}
	}

}
